package com.vs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.vs.common.DuplicateTypeException;
import com.vs.model.ExpenseTypeForm;

public class ExpensesTypeOperation {

	private String expenseTypeInsertString = "insert into expense_type(expense_name,added_by) values(?,?)";
	private String expenseTypeDeleteString = "delete from expense_type where expense_type_id=?";
	private String getExpenseTypesString = "select expense_type_id,expense_name from expense_type";
	private String getAllExpenseTypesString = "call get_expense_type_list()";


	public boolean deleteExpenseType(Integer expenseTypeId ) {
		Connection conn = null;
		boolean success = false;
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				//System.out.println("conn found");
			}
			PreparedStatement ps = conn
					.prepareStatement(expenseTypeDeleteString);
			ps.setInt(1, expenseTypeId);
			ps.execute();
			ps.close();
			conn.commit();
			success = true;

		} catch (Exception e) {
			e.printStackTrace();
			success = false;
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return success;
		
	}
	
	public boolean addExpenseType(ExpenseTypeForm expenseTypeForm) throws DuplicateTypeException{
		Connection conn = null;
		boolean success = false;
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				//System.out.println("conn found");
			}
			PreparedStatement psExpenseTypeInsert = conn
					.prepareStatement(expenseTypeInsertString);
			psExpenseTypeInsert.setString(1, expenseTypeForm.getExpenseType());
			psExpenseTypeInsert.setString(2, expenseTypeForm.getAddedBy());
			psExpenseTypeInsert.execute();
			psExpenseTypeInsert.close();
			conn.commit();
			success = true;

		} catch (Exception e) {
			e.printStackTrace();
			success = false;
			throw new DuplicateTypeException(e);
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return success;
	}

	public List<ExpenseTypeForm> getExpenseTypeLOV() {
		Connection conn = null;
		List<ExpenseTypeForm> expenseTypeList = new ArrayList<ExpenseTypeForm>();

		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				//System.out.println("conn found");
			}
			PreparedStatement psExpenseTypeList = conn
					.prepareStatement(getExpenseTypesString);
			// psExpenseTypeInsert.setString(1,
			// expenseTypeForm.getExpenseType());
			ResultSet rs = psExpenseTypeList.executeQuery();
			while (rs.next()) {
				ExpenseTypeForm expenseType = new ExpenseTypeForm();
				expenseType.setExpenseTypeId(rs.getInt(1));
				expenseType.setExpenseType(rs.getString(2));
				expenseTypeList.add(expenseType);
			}
			psExpenseTypeList.close();
			conn.commit();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return expenseTypeList;
	}

	public List<ExpenseTypeForm> getAllExpenseTypes() {
		Connection conn = null;
		List<ExpenseTypeForm> expenseTypeList = new ArrayList<ExpenseTypeForm>();
		System.out.println("inside getAllExpenseTypes");
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				//System.out.println("conn found");
			}
			PreparedStatement ps = conn
					.prepareStatement(getAllExpenseTypesString);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				ExpenseTypeForm expenseType = new ExpenseTypeForm();
				expenseType.setExpenseTypeId(rs.getInt(1));
				expenseType.setExpenseType(rs.getString(2));
				expenseTypeList.add(expenseType);
			}
			ps.close();
			conn.commit();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		System.out.println("AllExpenseTypes size:" + expenseTypeList.size());
		return expenseTypeList;
	}

}
